cd "S:\Katja_Aksel\JFQA index paper\v3\pseudo_data"
clear
set more 1
cap log close

log using ./logs/Tables1_3.log, replace

**********************************************
***Table 1
use ./1k_population_clean.dta, clear
count
*124,348
tab vcbacked
*928 firms with at least one VC investment
tab sample
*51,243
tab sample vcbacked
/*
           |       vcbacked
    sample |         0          1 |     Total
-----------+----------------------+----------
         0 |    72,990        115 |    73,105 
         1 |    50,430        813 |    51,243 
-----------+----------------------+----------
     Total |   123,420        928 |   124,348 
*/
tab innovindustry vcbacked
*79,196, thereof 861 VC-backed
tab english vcbacked
*35,200, thereof 555 VC-backed
tab innhub vcbacked
*34,217, thereof 522 VC-backed
tab firstyr vcbacked
*20,011, thereof 413 VC-backed


**how many firms with at least one financing round
use ./4_postmoney_valuations, clear
keep if mv_rounds!=. 
keep orgnr time mv_rounds
bys orgnr: gen help=_N
keep orgnr help
duplicates drop
count if help>0
*45,725 firms
count if help>1
*9,698 firms

**how many firms have at least one secondary purchase
use ./4_postmoney_valuations, clear
keep if mv_sec!=.
keep orgnr 
duplicates drop
count
*24,814 firms

**how many firms do not have any transactions
use ./4_postmoney_valuations, clear
merge m:1 orgnr using ./1k_population_clean.dta
/*
   Result                      Number of obs
    -----------------------------------------
    Not matched                        69,664
        from master                         0  (_merge==1)
        from using                     69,664  (_merge==2)

    Matched                           105,809  (_merge==3)
    -----------------------------------------

*/
**69,664 firms do not have any equity market transactions

**HIP firms with at least one equity financing round
use ./5_postmoney_valuations_failures.dta, clear
keep if mv_rounds!=. & mv_rounds!=0
keep if sample==1
keep orgnr
duplicates drop
*19,721 firms

***HIP VC-backed firms (VC-backed firms with market valuations)
use ./5_postmoney_valuations_failures.dta, clear
keep if vcbacked==1
drop if (mv_rounds==0 | mv_rounds==.)  & (mv_sec==0 | mv_sec==.)
keep orgnr sample
duplicates drop
tab sample
/*

     sample |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |        109       12.22       12.22
          1 |        783       87.78      100.00
------------+-----------------------------------
      Total |        892      100.00

*/
**********************************************
**********************************************

**********************************************
***Table 2
**check whether firms have innovation grants
use "S:\Katja_Aksel\JFQA index paper\v3\pseudo_data\IN data after 2005.dta", clear
drop if loan==1
keep orgnr
duplicates drop
merge 1:1 orgnr using ./1k_population_clean.dta
gen grant=(_merge==3)
drop if _merge==1
drop _merge
**check whether the firms have patent applications
merge 1:m orgnr using "S:\Katja_Aksel\JFQA index paper\v3\pseudo_data\patents_20211206.dta", keepusing(orgnr)
drop if _merge==2
gen patapplication=(_merge==3)
drop _merge
duplicates drop
save ./1l_population_clean.dta, replace

**add revenues from the accounting data
use ./1l_population_clean.dta, clear
merge 1:m orgnr using  "S:\Katja_Aksel\JFQA index paper\v3\pseudo_data\1992-2018accounting.dta", keepusing(aar salgsinn) 
keep if _merge==3
keep orgnr aar founded salgsinn 
sort orgnr aar
bys orgnr: gen lauf=_n
gen age=aar-founded
replace salgsinn=0 if salgsinn<0
gen rev0=salgsinn if lauf==1 & age<4
gen rev4=salgsinn if age==4
bys orgnr: egen revenue_0=min(rev0)
bys orgnr: egen revenue_4=min(rev4)
keep orgnr revenue* 
duplicates drop

**generate a variable for 4-year-revenue-growth
gen revgrowth4=(revenue_4-revenue_0)/revenue_0
keep orgnr revgrowth4 
merge 1:1 orgnr using ./1l_population_clean.dta

**generate a dummy for successful exit
gen exit=(ipoyear!=. | acquisition!=. | mergeryear!=.)
winsor revgrowth4, gen(revgr4_w) p(0.01)
drop _merge

**Logit regression for dummy variables
foreach t in patapplication vcbacked grant exit {
	logit `t' i.english i.founded, vce(robust)
outreg2 using .\tables/Table2.xls, append ctitle (`t') br coefastr bdec(3) excel alpha(0.001, 0.01, 0.05) keep (`t' i.english) addstat(Pseudo R2, e(r2_p)) addtext (Inception cohort FE, Yes)
	logit `t' i.innhub i.founded, vce(robust)
outreg2 using .\tables/Table2.xls, append ctitle (`t') br coefastr bdec(3) excel alpha(0.001, 0.01, 0.05) keep (`t' i.innhub) addstat(Pseudo R2, e(r2_p)) addtext (Inception cohort FE, Yes)
	logit `t' i.innovindustry i.founded, vce(robust)
outreg2 using .\tables/Table2.xls, append ctitle (`t') br coefastr bdec(3) excel alpha(0.001, 0.01, 0.05) keep (`t' i.innovindustry) addstat(Pseudo R2, e(r2_p)) addtext (Inception cohort FE, Yes)
	logit `t' i.firstyrfarboard i.founded, vce(robust)
outreg2 using .\tables/Table2.xls, append ctitle (`t') br coefastr bdec(3) excel alpha(0.001, 0.01, 0.05) keep (`t' i.firstyrfarboard) addstat(Pseudo R2, e(r2_p)) addtext (Inception cohort FE, Yes)
	logit `t' i.english i.innhub i.innovindustry i.firstyrfarboard i.founded, vce(robust)
outreg2 using .\tables/Table2.xls, append ctitle (`t') br coefastr bdec(3) excel alpha(0.001, 0.01, 0.05) keep (`t' i.english i.innhub i.innovindustry i.firstyrfarboard) addstat(Pseudo R2, e(r2_p)) addtext (Inception cohort FE, Yes)
}

**OLS regression for revenues
	regress revgr4_w i.english i.founded, vce(robust)
outreg2 using .\tables/Table2.xls, append ctitle (`t') br coefastr bdec(3) excel alpha(0.001, 0.01, 0.05) keep (revgr4_w i.english) addtext (Inception cohort FE, Yes)
	regress revgr4_w i.innhub i.founded, vce(robust)
outreg2 using .\tables/Table2.xls, append ctitle (`t') br coefastr bdec(3) excel alpha(0.001, 0.01, 0.05) keep (revgr4_w i.innhub) addtext (Inception cohort FE, Yes)
	regress revgr4_w i.innovindustry i.founded, vce(robust)
outreg2 using .\tables/Table2.xls, append ctitle (`t') br coefastr bdec(3) excel alpha(0.001, 0.01, 0.05) keep (revgr4_w i.innovindustry)  addtext (Inception cohort FE, Yes)
	regress revgr4_w i.firstyrfarboard i.founded, vce(robust)
outreg2 using .\tables/Table2.xls, append ctitle (`t') br coefastr bdec(3) excel alpha(0.001, 0.01, 0.05) keep (revgr4_w i.firstyrfarboard)  addtext (Inception cohort FE, Yes)
	regress revgr4_w i.english i.innhub i.innovindustry i.firstyrfarboard i.founded, vce(robust)
outreg2 using .\tables/Table2.xls, append ctitle (`t') br coefastr bdec(3) excel alpha(0.001, 0.01, 0.05) keep (revgr4_w i.english i.innhub i.innovindustry i.firstyrfarboard) addtext (Inception cohort FE, Yes)
**********************************************

**********************************************
***Table 3
**Purchases
use ./2c_all_purchases.dta, clear
replace amount=amount/1000000
keep amount erverv_type sample vcbacked
gen primall=amount if erverv_type!="K"
gen secall=amount if erverv_type=="K"
gen primbroad=amount if erverv_type!="K" & sample==1
gen secbroad=amount if erverv_type=="K" & sample==1
gen primbroadpe=amount if erverv_type!="K" & sample==1 & vcbacked==1
gen secbroadpe=amount if erverv_type=="K" & sample==1 & vcbacked==1
gen primnone=amount if erverv_type!="K" & sample==0
gen secnone=amount if erverv_type=="K" & sample==0


foreach t in primall secall primbroad secbroad primbroadpe secbroadpe primnone secnone {
	replace `t'=0 if `t'==.
	egen agg`t'=sum(`t')
}
keep agg*
duplicates drop
sum

**Realizations
use ./1b_realization_file.dta, clear 
replace ramount=ramount/1000000
keep ramount realisasjon sample vcbacked
gen saleall=ramount if realisasjon=="R"
gen liqall=ramount if realisasjon!="R" 

gen salebroad=ramount if realisasjon=="R" & sample==1
gen liqbroad=ramount if realisasjon!="R" & sample==1

gen salebroadpe=ramount if realisasjon=="R" & sample==1 & vcbacked==1
gen liqbroadpe=ramount if realisasjon!="R" & sample==1 & vcbacked==1

gen salenone=ramount if realisasjon=="R" & sample==0
gen liqnone=ramount if realisasjon!="R" & sample==0


foreach t in saleall liqall salebroad liqbroad salebroadpe liqbroadpe salenone liqnone {
	replace `t'=0 if `t'==.
	egen agg`t'=sum(`t')
}
keep agg*
duplicates drop
sum
**********************************************





